//
// Community.CsharpSqlite.SQLiteClient.SqliteConnection.cs
//
// Represents an open connection to a Sqlite database file.
//
// Author(s): Vladimir Vukicevic  <vladimir@pobox.com>
//            Everaldo Canuto  <everaldo_canuto@yahoo.com.br>
//            Daniel Morgan <monodanmorg@yahoo.com>
//            Noah Hart <Noah.Hart@gmail.com>
//
// Copyright (C) 2002  Vladimir Vukicevic
//
// Permission is hereby granted, free of charge, to any person obtaining
// a copy of this software and associated documentation files (the
// "Software"), to deal in the Software without restriction, including
// without limitation the rights to use, copy, modify, merge, publish,
// distribute, sublicense, and/or sell copies of the Software, and to
// permit persons to whom the Software is furnished to do so, subject to
// the following conditions:
// 
// The above copyright notice and this permission notice shall be
// included in all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
//

using System;
using System.Data;
#if NET_2_0
using System.IO;
using System.Data.Common;
#endif
using System.Text;
using Community.CsharpSqlite;

namespace Community.CsharpSqlite.SQLiteClient
{
#if NET_2_0
	public class SqliteConnection : DbConnection, ICloneable
#else
#if !SILVERLIGHT
	public class SqliteConnection : IDbConnection, ICloneable
#else
    public class SqliteConnection : IDisposable
#endif
#endif
	{

#region Fields
		
		private string conn_str;
		private string db_file;
		private int db_mode;
		private int db_version;
		private IntPtr sqlite_handle;
        private Sqlite3.sqlite3 sqlite_handle2;
		private ConnectionState state;
		private Encoding encoding;
		private int busy_timeout;
#if NET_2_0
		bool disposed;
#endif

#endregion

#region Constructors and destructors
		
		public SqliteConnection ()
		{
			db_file = null;
			db_mode = 0644;
			db_version = 3;
			state = ConnectionState.Closed;
			sqlite_handle = IntPtr.Zero;
			encoding = null;
			busy_timeout = 0;
		}
		
		public SqliteConnection (string connstring) : this ()
		{
			ConnectionString = connstring;
		}


#if !NET_2_0
		public void Dispose ()
		{
			Close ();
		}
#else
		protected override void Dispose (bool disposing)
		{
			try {
				if (disposing && !disposed) {
					Close ();
					conn_str = null;
				}
			} finally {
				disposed = true;
				base.Dispose (disposing);
			}
		}
#endif
		                
#endregion

#region Properties
		
#if NET_2_0
		override
#endif
			public string ConnectionString {
			get { return conn_str; }
			set { SetConnectionString(value); }
		}
		
#if NET_2_0
		override
#endif
			public int ConnectionTimeout {
			get { return 0; }
		}
	
#if NET_2_0
		override
#endif
			public string Database {
			get { return db_file; }
		}
		
#if NET_2_0
		override
#endif
			public ConnectionState State {
			get { return state; }
		}
		
		public Encoding Encoding {
			get { return encoding; }
		}

		public int Version {
			get { return db_version; }
		}

#if NET_2_0
      override
#endif
      public string ServerVersion
      {
        get { return Sqlite3.sqlite3_libversion(); }
      }

      internal Sqlite3.sqlite3 Handle2
		    {
                get { return sqlite_handle2; }
		    }
		internal IntPtr Handle {
			get { return sqlite_handle; }
		}
		
#if NET_2_0
		public override string DataSource {
			get { return db_file; }
		}

#endif

    public int LastInsertRowId {
			get {
				//if (Version == 3)
				    return (int) Sqlite3.sqlite3_last_insert_rowid(Handle2);
					//return (int)Sqlite.sqlite3_last_insert_rowid (Handle);
				//else
				//	return Sqlite.sqlite_last_insert_rowid (Handle);
			}
		}

		public int BusyTimeout {
			get {
				return busy_timeout;  
			}
			set {
			  	busy_timeout = value < 0 ? 0 : value;
			}
		}
		
#endregion

#region Private Methods
		
		private void SetConnectionString(string connstring)
		{
			if (connstring == null) {
				Close ();
				conn_str = null;
				return;
			}
			
			if (connstring != conn_str) {
				Close ();
				conn_str = connstring;
				
				db_file = null;
				db_mode = 0644;
				
				string[] conn_pieces = connstring.Split (',');
				for (int i = 0; i < conn_pieces.Length; i++) {
					string piece = conn_pieces [i].Trim ();
					if (piece.Length == 0) { // ignore empty elements
                                                continue;
					}
					string[] arg_pieces = piece.Split ('=');
					if (arg_pieces.Length != 2) {
						throw new InvalidOperationException ("Invalid connection string");
					}
					string token = arg_pieces[0].ToLower (System.Globalization.CultureInfo.InvariantCulture).Trim ();
					string tvalue = arg_pieces[1].Trim ();
					string tvalue_lc = arg_pieces[1].ToLower (System.Globalization.CultureInfo.InvariantCulture).Trim ();
					switch (token) {
#if NET_2_0
						case "DataSource":
#endif
						case "uri": 
							if (tvalue_lc.StartsWith ("file://")) {
								db_file = tvalue.Substring (7);
							} else if (tvalue_lc.StartsWith ("file:")) {
								db_file = tvalue.Substring (5);
							} else if (tvalue_lc.StartsWith ("/")) {
								db_file = tvalue;
#if NET_2_0
							} else if (tvalue_lc.StartsWith ("|DataDirectory|",
											 StringComparison.InvariantCultureIgnoreCase)) {
								AppDomainSetup ads = AppDomain.CurrentDomain.SetupInformation;
								string filePath = String.Format ("App_Data{0}{1}",
												 Path.DirectorySeparatorChar,
												 tvalue_lc.Substring (15));
								
								db_file = Path.Combine (ads.ApplicationBase, filePath);
#endif
							} else {
								throw new InvalidOperationException ("Invalid connection string: invalid URI");
							}
							break;

						case "mode": 
							db_mode = Convert.ToInt32 (tvalue);
							break;

						case "version":
							db_version = Convert.ToInt32 (tvalue);
              if (db_version < 3) throw new InvalidOperationException ("Minimum database version is 3");
							break;

						case "encoding": // only for sqlite2
							encoding = Encoding.GetEncoding (tvalue);
							break;

						case "busy_timeout":
							busy_timeout = Convert.ToInt32 (tvalue);
							break;
					}
				}
				
				if (db_file == null) {
					throw new InvalidOperationException ("Invalid connection string: no URI");
				}
			}
		}		
#endregion

#region Internal Methods
		
		internal void StartExec ()
		{
			// use a mutex here
			state = ConnectionState.Executing;
		}
		
		internal void EndExec ()
		{
			state = ConnectionState.Open;
		}
		
#endregion

#region Public Methods

#if !SILVERLIGHT
		object ICloneable.Clone ()
		{
			return new SqliteConnection (ConnectionString);
		}
#endif

#if SILVERLIGHT

        public SqliteTransaction BeginTransaction()
        {
            if (state != ConnectionState.Open)
                throw new InvalidOperationException("Invalid operation: The connection is closed");

            SqliteTransaction t = new SqliteTransaction();
            t.Connection = this;

            SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
            cmd.CommandText = "BEGIN";
            cmd.ExecuteNonQuery();
            return t;
        }
#else

#if NET_2_0
		// [MonoTODO ("handle IsolationLevel")]
		protected override DbTransaction BeginDbTransaction (IsolationLevel il)
#else
		public IDbTransaction BeginTransaction ()
#endif
		{
			if (state != ConnectionState.Open)
				throw new InvalidOperationException("Invalid operation: The connection is closed");
			
			SqliteTransaction t = new SqliteTransaction();
#if NET_2_0
			t.SetConnection (this);
#else
			t.Connection = this;
#endif
			SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
			cmd.CommandText = "BEGIN";
			cmd.ExecuteNonQuery();
			return t;
		}

#if NET_2_0
		public new DbTransaction BeginTransaction ()
			{
				return BeginDbTransaction (IsolationLevel.Unspecified);
		}

			public new DbTransaction BeginTransaction (IsolationLevel il)
				{
					return BeginDbTransaction (il);
			}
#else
		public IDbTransaction BeginTransaction (IsolationLevel il)
		{
			throw new InvalidOperationException();
		}
#endif

#endif
		
#if NET_2_0
		override
#endif
		public void Close ()
		{
			if (state != ConnectionState.Open) {
				return;
			}
			
			state = ConnectionState.Closed;
		
			if (Version == 3)
                //Sqlite3.sqlite3_close()
				Sqlite3.sqlite3_close (sqlite_handle2);
			//else 
				//Sqlite.sqlite_close (sqlite_handle);
			sqlite_handle = IntPtr.Zero;
		}
		
#if NET_2_0
		override
#endif
		public void ChangeDatabase (string databaseName)
		{
			Close ();
			db_file = databaseName;
			Open ();
		}
		
#if !NET_2_0 && !SILVERLIGHT
		IDbCommand IDbConnection.CreateCommand ()
		{
			return CreateCommand ();
		}
#endif
		
#if NET_2_0
		protected override DbCommand CreateDbCommand ()
#else
			public SqliteCommand CreateCommand ()
#endif
		{
			return new SqliteCommand (null, this);
		}
		
#if NET_2_0
		override
#endif
		public void Open ()
		{
			if (conn_str == null) {
				throw new InvalidOperationException ("No database specified");
			}
			
			if (state != ConnectionState.Closed) {
				return;
			}
			
			IntPtr errmsg = IntPtr.Zero;
            /*
			if (Version == 2){
				try {
					sqlite_handle = Sqlite.sqlite_open(db_file, db_mode, out errmsg);
					if (errmsg != IntPtr.Zero) {
						string msg = Marshal.PtrToStringAnsi (errmsg);
						Sqlite.sqliteFree (errmsg);
						throw new ApplicationException (msg);
					}
				} catch (DllNotFoundException) {
					db_version = 3;
				} catch (EntryPointNotFoundException) {
					db_version = 3;
				}
				
				if (busy_timeout != 0)
					Sqlite.sqlite_busy_timeout (sqlite_handle, busy_timeout);
			}
             */
			if (Version == 3) {
			    sqlite_handle = (IntPtr)1;
			    int err = Sqlite3.sqlite3_open(db_file, ref sqlite_handle2);
				//int err = Sqlite.sqlite3_open16(db_file, out sqlite_handle);
				if (err == (int)SqliteError.ERROR)
                    throw new ApplicationException (Sqlite3.sqlite3_errmsg(sqlite_handle2));
					//throw new ApplicationException (Marshal.PtrToStringUni( Sqlite.sqlite3_errmsg16 (sqlite_handle)));
                if (busy_timeout != 0)
                    Sqlite3.sqlite3_busy_timeout(sqlite_handle2, busy_timeout);
					//Sqlite.sqlite3_busy_timeout (sqlite_handle, busy_timeout);
			} else {
			}
			state = ConnectionState.Open;
		}


        void ValidateIdentifier(string value)
        {
            if (value.Contains("'"))
                throw new ArgumentException("Identifiers can not contain a single quote.");
        }

// Functions which use data table and not vital to normal operation
#if !SILVERLIGHT

        public DataTable GetSchema(String collectionName)
        {
            return GetSchema(collectionName, null);
        }

        public DataTable GetSchema(String collectionName, string[] restrictionValues)
        {
            if (State != ConnectionState.Open)
                throw new InvalidOperationException("Invalid operation.  The connection is closed.");

            int restrictionsCount = 0;
            if (restrictionValues != null)
                restrictionsCount = restrictionValues.Length;

            DataTable metaTable = GetSchemaMetaDataCollections();
            foreach (DataRow row in metaTable.Rows)
            {
                if (String.Compare(row["CollectionName"].ToString(), collectionName, true) == 0)
                {
                    int restrictions = (int)row["NumberOfRestrictions"];
                    if (restrictionsCount > restrictions)
                        throw new ArgumentException("More restrictions were provided than needed.");
                }
            }

            switch (collectionName.ToUpper())
            {
                case "METADATACOLLECTIONS":
                    return metaTable;
                case "DATASOURCEINFORMATION":
                    return GetSchemaDataSourceInformation();
                case "DATATYPES":
                    return GetSchemaDataTypes();
                case "RESTRICTIONS":
                    return GetSchemaRestrictions();
                case "RESERVEDWORDS":
                    return GetSchemaReservedWords();
                case "TABLES":
                    return GetSchemaTables(restrictionValues);
                case "COLUMNS":
                    return GetSchemaColumns(restrictionValues);
                case "VIEWS":
                    return GetSchemaViews(restrictionValues);
                case "INDEXCOLUMNS":
                    return GetSchemaIndexColumns(restrictionValues);
                case "INDEXES":
                    return GetSchemaIndexes(restrictionValues);
                case "UNIQUEKEYS":
                    throw new NotImplementedException(collectionName);
                case "PRIMARYKEYS":
                    throw new NotImplementedException(collectionName);
                case "FOREIGNKEYS":
                    return GetSchemaForeignKeys(restrictionValues);
                case "FOREIGNKEYCOLUMNS":
                    throw new NotImplementedException(collectionName);
                case "TRIGGERS":
                    return GetSchemaTriggers(restrictionValues);
            }

            throw new ArgumentException("The requested collection is not defined.");
        }

        static DataTable metaDataCollections = null;
        DataTable GetSchemaMetaDataCollections()
        {
            if (metaDataCollections != null)
                return metaDataCollections;

            DataTable dt = new DataTable();

            dt.Columns.Add("CollectionName", typeof(System.String));
            dt.Columns.Add("NumberOfRestrictions", typeof(System.Int32));
            dt.Columns.Add("NumberOfIdentifierParts", typeof(System.Int32));

            dt.LoadDataRow(new object[] { "MetaDataCollections", 0, 0 }, true);
            dt.LoadDataRow(new object[] { "DataSourceInformation", 0, 0 }, true);
            dt.LoadDataRow(new object[] { "DataTypes", 0, 0 }, true);
            dt.LoadDataRow(new object[] { "Restrictions", 0, 0 }, true);
            dt.LoadDataRow(new object[] { "ReservedWords", 0, 0 }, true);
            dt.LoadDataRow(new object[] { "Tables", 1, 1 }, true);
            dt.LoadDataRow(new object[] { "Columns", 1, 1 }, true);
            dt.LoadDataRow(new object[] { "Views", 1, 1 }, true);
            dt.LoadDataRow(new object[] { "IndexColumns", 1, 1 }, true);
            dt.LoadDataRow(new object[] { "Indexes", 1, 1 }, true);
            //dt.LoadDataRow(new object[] { "UniqueKeys", 1, 1 }, true);
            //dt.LoadDataRow(new object[] { "PrimaryKeys", 1, 1 }, true);
            dt.LoadDataRow(new object[] { "ForeignKeys", 1, 1 }, true);
            //dt.LoadDataRow(new object[] { "ForeignKeyColumns", 1, 1 }, true);
            dt.LoadDataRow(new object[] { "Triggers", 1, 1 }, true);

            return dt;
        }

        DataTable GetSchemaRestrictions()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("CollectionName", typeof(System.String));
            dt.Columns.Add("RestrictionName", typeof(System.String));
            dt.Columns.Add("ParameterName", typeof(System.String));
            dt.Columns.Add("RestrictionDefault", typeof(System.String));
            dt.Columns.Add("RestrictionNumber", typeof(System.Int32));

            dt.LoadDataRow(new object[] { "Tables", "Table", "TABLENAME", "TABLE_NAME", 1 }, true);
            dt.LoadDataRow(new object[] { "Columns", "Table", "TABLENAME", "TABLE_NAME", 1 }, true);
            dt.LoadDataRow(new object[] { "Views", "View", "VIEWNAME", "VIEW_NAME", 1 }, true);
            dt.LoadDataRow(new object[] { "IndexColumns", "Name", "NAME", "INDEX_NAME", 1 }, true);
            dt.LoadDataRow(new object[] { "Indexes", "TableName", "TABLENAME", "TABLE_NAME", 1 }, true);
            dt.LoadDataRow(new object[] { "ForeignKeys", "Foreign_Key_Table_Name", "TABLENAME", "TABLE_NAME", 1 }, true);
            dt.LoadDataRow(new object[] { "Triggers", "TableName", "TABLENAME", "TABLE_NAME", 1 }, true);

            return dt;
        }

        DataTable GetSchemaTables(string[] restrictionValues)
        {
            SqliteCommand cmd = new SqliteCommand(
                          "SELECT type, name, tbl_name, rootpage, sql " +
                          " FROM sqlite_master " +
                          " WHERE (name = :pname or (:pname is null)) " +
                          " AND type = 'table' " +
                          " ORDER BY name", this);
            cmd.Parameters.Add("pname", DbType.String).Value = DBNull.Value;
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaColumns(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException("Columns must contain at least one restriction value for the table name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = (SqliteCommand)CreateCommand();
            cmd.CommandText = string.Format("PRAGMA table_info({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaTriggers(string[] restrictionValues)
        {
            SqliteCommand cmd = new SqliteCommand(
                          "SELECT type, name, tbl_name, rootpage, sql " +
                          " FROM sqlite_master " +
                          " WHERE (tbl_name = :pname or :pname is null) " +
                          " AND type = 'trigger' " +
                          " ORDER BY name", this);
            cmd.Parameters.Add("pname", DbType.String).Value = DBNull.Value;
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaIndexColumns(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException("IndexColumns must contain at least one restriction value for the index name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = (SqliteCommand)CreateCommand();
            cmd.CommandText = string.Format("PRAGMA index_info({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaIndexes(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException("Indexes must contain at least one restriction value for the table name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = (SqliteCommand)CreateCommand();
            cmd.CommandText = string.Format("PRAGMA index_list({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaForeignKeys(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException("Foreign Keys must contain at least one restriction value for the table name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = (SqliteCommand)CreateCommand();
            cmd.CommandText = string.Format("PRAGMA foreign_key_list({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaViews(string[] restrictionValues)
        {
            SqliteCommand cmd = new SqliteCommand(
                          "SELECT type, name, tbl_name, rootpage, sql " +
                          " FROM sqlite_master " +
                          " WHERE (name = :pname or :pname is null) " +
                          " AND type = 'view' " +
                          " ORDER BY name", this);
            cmd.Parameters.Add("pname", DbType.String).Value = DBNull.Value;
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        DataTable GetSchemaDataSourceInformation()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("CompositeIdentifierSeparatorPattern", typeof(System.String));
            dt.Columns.Add("DataSourceProductName", typeof(System.String));
            dt.Columns.Add("DataSourceProductVersion", typeof(System.String));
            dt.Columns.Add("DataSourceProductVersionNormalized", typeof(System.String));
            dt.Columns.Add("GroupByBehavior", typeof(System.Data.Common.GroupByBehavior));
            dt.Columns.Add("IdentifierPattern", typeof(System.String));
            dt.Columns.Add("IdentifierCase", typeof(System.Data.Common.IdentifierCase));
            dt.Columns.Add("OrderByColumnsInSelect", typeof(System.Boolean));
            dt.Columns.Add("ParameterMarkerFormat", typeof(System.String));
            dt.Columns.Add("ParameterMarkerPattern", typeof(System.String));
            dt.Columns.Add("ParameterNameMaxLength", typeof(System.Int32));
            dt.Columns.Add("ParameterNamePattern", typeof(System.String));
            dt.Columns.Add("QuotedIdentifierPattern", typeof(System.String));
            dt.Columns.Add("QuotedIdentifierCase", typeof(System.Data.Common.IdentifierCase));
            dt.Columns.Add("StatementSeparatorPattern", typeof(System.String));
            dt.Columns.Add("StringLiteralPattern", typeof(System.String));
            dt.Columns.Add("SupportedJoinOperators", typeof(System.Data.Common.SupportedJoinOperators));

            // TODO: set correctly
            dt.LoadDataRow(new object[] { "",
                                "SQLite",
                                ServerVersion,
                                ServerVersion,
                                3,
                                "",
                                1,
                                false,
                                "",
                                "",
                                30,
                                "",
                                2,
                                DBNull.Value,
                                "" },
                true);

            return dt;
        }

        DataTable GetSchemaDataTypes()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("TypeName", typeof(System.String));
            dt.Columns.Add("ProviderDbType", typeof(System.String));
            dt.Columns.Add("StorageType", typeof(System.Int32));
            dt.Columns.Add("DataType", typeof(System.String));
            // TODO: fill the rest of these
            /*
            dt.Columns.Add("ColumnSize", typeof(System.Int64));
            dt.Columns.Add("CreateFormat", typeof(System.String));
            dt.Columns.Add("CreateParameters", typeof(System.String));
            dt.Columns.Add("IsAutoIncrementable",typeof(System.Boolean));
            dt.Columns.Add("IsBestMatch", typeof(System.Boolean));
            dt.Columns.Add("IsCaseSensitive", typeof(System.Boolean));
            dt.Columns.Add("IsFixedLength", typeof(System.Boolean));
            dt.Columns.Add("IsFixedPrecisionScale",typeof(System.Boolean));
            dt.Columns.Add("IsLong", typeof(System.Boolean));
            dt.Columns.Add("IsNullable", typeof(System.Boolean));
            dt.Columns.Add("IsSearchable", typeof(System.Boolean));
            dt.Columns.Add("IsSearchableWithLike",typeof(System.Boolean));
            dt.Columns.Add("IsUnsigned", typeof(System.Boolean));
            dt.Columns.Add("MaximumScale", typeof(System.Int16));
            dt.Columns.Add("MinimumScale", typeof(System.Int16));
            dt.Columns.Add("IsConcurrencyType",typeof(System.Boolean));
            dt.Columns.Add("IsLiteralSupported",typeof(System.Boolean));
            dt.Columns.Add("LiteralPrefix", typeof(System.String));
            dt.Columns.Add("LiteralSuffix", typeof(System.String));
            */

            dt.LoadDataRow(new object[] { "INT", "INTEGER", 1, "System.Int32" }, true);
            dt.LoadDataRow(new object[] { "INTEGER", "INTEGER", 1, "System.Int32" }, true);
            dt.LoadDataRow(new object[] { "TINYINT", "INTEGER", 1, "System.Byte" }, true);
            dt.LoadDataRow(new object[] { "SMALLINT", "INTEGER", 1, "System.Int16" }, true);
            dt.LoadDataRow(new object[] { "MEDIUMINT", "INTEGER", 1, "System.Int32" }, true);
            dt.LoadDataRow(new object[] { "BIGINT", "INTEGER", 1, "System.Int64" }, true);
            dt.LoadDataRow(new object[] { "UNSIGNED BIGINT", "INTEGER", 1, "System.UInt64" }, true);
            dt.LoadDataRow(new object[] { "INT2", "INTEGER", 1, "System.Int16" }, true);
            dt.LoadDataRow(new object[] { "INT8", "INTEGER", 1, "System.Int64" }, true);

            dt.LoadDataRow(new object[] { "CHARACTER", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "VARCHAR", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "VARYING CHARACTER", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "NCHAR", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "NATIVE CHARACTER", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "NVARHCAR", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "TEXT", "TEXT", 2, "System.String" }, true);
            dt.LoadDataRow(new object[] { "CLOB", "TEXT", 2, "System.String" }, true);

            dt.LoadDataRow(new object[] { "BLOB", "NONE", 3, "System.Byte[]" }, true);

            dt.LoadDataRow(new object[] { "REAL", "REAL", 4, "System.Double" }, true);
            dt.LoadDataRow(new object[] { "DOUBLE", "REAL", 4, "System.Double" }, true);
            dt.LoadDataRow(new object[] { "DOUBLE PRECISION", "REAL", 4, "System.Double" }, true);
            dt.LoadDataRow(new object[] { "FLOAT", "REAL", 4, "System.Double" }, true);

            dt.LoadDataRow(new object[] { "NUMERIC", "NUMERIC", 5, "System.Decimal" }, true);
            dt.LoadDataRow(new object[] { "DECIMAL", "NUMERIC", 5, "System.Decimal" }, true);
            dt.LoadDataRow(new object[] { "BOOLEAN", "NUMERIC", 5, "System.Boolean" }, true);
            dt.LoadDataRow(new object[] { "DATE", "NUMERIC", 5, "System.DateTime" }, true);
            dt.LoadDataRow(new object[] { "DATETIME", "NUMERIC", 5, "System.DateTime" }, true);

            return dt;
        }

        DataTable GetSchemaReservedWords()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("ReservedWord", typeof(System.String));

            dt.LoadDataRow(new object[] { "ABORT" }, true);
            dt.LoadDataRow(new object[] { "ACTION" }, true);
            dt.LoadDataRow(new object[] { "ADD" }, true);
            dt.LoadDataRow(new object[] { "AFTER" }, true);
            dt.LoadDataRow(new object[] { "ALL" }, true);
            dt.LoadDataRow(new object[] { "ANALYZE" }, true);
            dt.LoadDataRow(new object[] { "AND" }, true);
            dt.LoadDataRow(new object[] { "AS" }, true);
            dt.LoadDataRow(new object[] { "ATTACH" }, true);
            dt.LoadDataRow(new object[] { "AUTOINCREMENT" }, true);
            dt.LoadDataRow(new object[] { "BEFORE" }, true);
            dt.LoadDataRow(new object[] { "BEFORE" }, true);
            dt.LoadDataRow(new object[] { "BEGIN" }, true);
            dt.LoadDataRow(new object[] { "BETWEEN" }, true);
            dt.LoadDataRow(new object[] { "BY" }, true);
            dt.LoadDataRow(new object[] { "CASCADE" }, true);
            dt.LoadDataRow(new object[] { "CASE" }, true);
            dt.LoadDataRow(new object[] { "CAST" }, true);
            dt.LoadDataRow(new object[] { "CHECK" }, true);
            dt.LoadDataRow(new object[] { "COLLATE" }, true);
            dt.LoadDataRow(new object[] { "COLUMN" }, true);
            dt.LoadDataRow(new object[] { "COMMIT" }, true);
            dt.LoadDataRow(new object[] { "CONFLICT" }, true);
            dt.LoadDataRow(new object[] { "CONTRAINT" }, true);
            dt.LoadDataRow(new object[] { "CREATE" }, true);
            dt.LoadDataRow(new object[] { "CROSS" }, true);
            dt.LoadDataRow(new object[] { "CURRENT_DATE" }, true);
            dt.LoadDataRow(new object[] { "CURRENT_TIME" }, true);
            dt.LoadDataRow(new object[] { "CURRENT_TIMESTAMP" }, true);
            dt.LoadDataRow(new object[] { "DATABASE" }, true);
            dt.LoadDataRow(new object[] { "DEFAULT" }, true);
            dt.LoadDataRow(new object[] { "DEFERRABLE" }, true);
            dt.LoadDataRow(new object[] { "DEFERRED" }, true);
            dt.LoadDataRow(new object[] { "DELETE" }, true);
            dt.LoadDataRow(new object[] { "DESC" }, true);
            dt.LoadDataRow(new object[] { "DETACH" }, true);
            dt.LoadDataRow(new object[] { "DISTINCT" }, true);
            dt.LoadDataRow(new object[] { "DROP" }, true);
            dt.LoadDataRow(new object[] { "EACH" }, true);
            dt.LoadDataRow(new object[] { "ELSE" }, true);
            dt.LoadDataRow(new object[] { "END" }, true);
            dt.LoadDataRow(new object[] { "ESCAPE" }, true);
            dt.LoadDataRow(new object[] { "EXCEPT" }, true);
            dt.LoadDataRow(new object[] { "EXCLUSIVE" }, true);
            dt.LoadDataRow(new object[] { "EXISTS" }, true);
            dt.LoadDataRow(new object[] { "EXPLAIN" }, true);
            dt.LoadDataRow(new object[] { "FAIL" }, true);
            dt.LoadDataRow(new object[] { "FOR" }, true);
            dt.LoadDataRow(new object[] { "FOREIGN" }, true);
            dt.LoadDataRow(new object[] { "FROM" }, true);
            dt.LoadDataRow(new object[] { "FULL" }, true);
            dt.LoadDataRow(new object[] { "GLOB" }, true);
            dt.LoadDataRow(new object[] { "GROUP" }, true);
            dt.LoadDataRow(new object[] { "HAVING" }, true);
            dt.LoadDataRow(new object[] { "IF" }, true);
            dt.LoadDataRow(new object[] { "IGNORE" }, true);
            dt.LoadDataRow(new object[] { "IMMEDIATE" }, true);
            dt.LoadDataRow(new object[] { "IN" }, true);
            dt.LoadDataRow(new object[] { "INDEX" }, true);
            dt.LoadDataRow(new object[] { "INITIALLY" }, true);
            dt.LoadDataRow(new object[] { "INNER" }, true);
            dt.LoadDataRow(new object[] { "INSERT" }, true);
            dt.LoadDataRow(new object[] { "INSTEAD" }, true);
            dt.LoadDataRow(new object[] { "INTERSECT" }, true);
            dt.LoadDataRow(new object[] { "INTO" }, true);
            dt.LoadDataRow(new object[] { "IS" }, true);
            dt.LoadDataRow(new object[] { "ISNULL" }, true);
            dt.LoadDataRow(new object[] { "JOIN" }, true);
            dt.LoadDataRow(new object[] { "KEY" }, true);
            dt.LoadDataRow(new object[] { "LEFT" }, true);
            dt.LoadDataRow(new object[] { "LIKE" }, true);
            dt.LoadDataRow(new object[] { "LIMIT" }, true);
            dt.LoadDataRow(new object[] { "MATCH" }, true);
            dt.LoadDataRow(new object[] { "NATURAL" }, true);
            dt.LoadDataRow(new object[] { "NO" }, true);
            dt.LoadDataRow(new object[] { "NOT" }, true);
            dt.LoadDataRow(new object[] { "NOT NULL" }, true);
            dt.LoadDataRow(new object[] { "OF" }, true);
            dt.LoadDataRow(new object[] { "OFFSET" }, true);
            dt.LoadDataRow(new object[] { "ON" }, true);
            dt.LoadDataRow(new object[] { "OR" }, true);
            dt.LoadDataRow(new object[] { "ORDER" }, true);
            dt.LoadDataRow(new object[] { "OUTER" }, true);
            dt.LoadDataRow(new object[] { "PLAN" }, true);
            dt.LoadDataRow(new object[] { "PRAGMA" }, true);
            dt.LoadDataRow(new object[] { "PRIMARY" }, true);
            dt.LoadDataRow(new object[] { "QUERY" }, true);
            dt.LoadDataRow(new object[] { "RAISE" }, true);
            dt.LoadDataRow(new object[] { "REFERENCES" }, true);
            dt.LoadDataRow(new object[] { "REGEXP" }, true);
            dt.LoadDataRow(new object[] { "REINDEX" }, true);
            dt.LoadDataRow(new object[] { "RELEASE" }, true);
            dt.LoadDataRow(new object[] { "RENAME" }, true);
            dt.LoadDataRow(new object[] { "REPLACE" }, true);
            dt.LoadDataRow(new object[] { "RESTRICT" }, true);
            dt.LoadDataRow(new object[] { "RIGHT" }, true);
            dt.LoadDataRow(new object[] { "ROLLBACK" }, true);
            dt.LoadDataRow(new object[] { "ROW" }, true);
            dt.LoadDataRow(new object[] { "SAVEPOOINT" }, true);
            dt.LoadDataRow(new object[] { "SELECT" }, true);
            dt.LoadDataRow(new object[] { "SET" }, true);
            dt.LoadDataRow(new object[] { "TABLE" }, true);
            dt.LoadDataRow(new object[] { "TEMP" }, true);
            dt.LoadDataRow(new object[] { "TEMPORARY" }, true);
            dt.LoadDataRow(new object[] { "THEN" }, true);
            dt.LoadDataRow(new object[] { "TO" }, true);
            dt.LoadDataRow(new object[] { "TRANSACTION" }, true);
            dt.LoadDataRow(new object[] { "TRIGGER" }, true);
            dt.LoadDataRow(new object[] { "UNION" }, true);
            dt.LoadDataRow(new object[] { "UNIQUE" }, true);
            dt.LoadDataRow(new object[] { "UPDATE" }, true);
            dt.LoadDataRow(new object[] { "USING" }, true);
            dt.LoadDataRow(new object[] { "VACUUM" }, true);
            dt.LoadDataRow(new object[] { "VALUES" }, true);
            dt.LoadDataRow(new object[] { "VIEW" }, true);
            dt.LoadDataRow(new object[] { "VIRTUAL" }, true);
            dt.LoadDataRow(new object[] { "WHEN" }, true);
            dt.LoadDataRow(new object[] { "WHERE" }, true);

            return dt;
        }

        DataTable GetSchemaDataTable(SqliteCommand cmd, string[] restrictionValues)
        {
            if (restrictionValues != null && cmd.Parameters.Count > 0)
            {
                for (int i = 0; i < restrictionValues.Length; i++)
                    cmd.Parameters[i].Value = restrictionValues[i];
            }

            SqliteDataAdapter adapter = new SqliteDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);

            return dt;
        }

#endif

#endregion
	}
}
